﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using DLL.工具类DAL;
using DAL;

namespace DAL
{
    public class IEQuestionEntityDAL
    {
        private readonly SQLHelper sqlhelper;

        #region "实例化SqlHelper"
        /// <summary>
        /// SqlHelper实例化
        /// </summary>
        public IEQuestionEntityDAL()
        {
            sqlhelper = new SQLHelper();
        }
        #endregion

        #region  根据题型选择出IE的题干--周洲--2015年11月21日
        /// <summary>
        /// 根据题型选择出IE的题干--周洲--2015年11月21日
        /// </summary>
        /// <param name="iequestion">传递考试类型</param>
        /// <returns>返回题目内容</returns>
        public DataTable LoadIEQuestion(IEQuestionEntity ieinfo)
        {

            DataTable ieQuestionDt ;
            string cmdText = "select * from IEQuestionEntity where paperType =@paperType";
            SqlParameter[] paras = new SqlParameter[]{new SqlParameter ("@paperType",ieinfo.paperType )
        };
            ieQuestionDt = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.Text);
            return ieQuestionDt;

        }
        #endregion


        #region "加载试题信息到界面中，以试卷类型判断，查出题型-韩梦甜-2015-11-20"
        /// <summary>
        /// 加载试题信息到界面中，以试卷类型判断，查出题型
        /// </summary>
        /// <param name="iequestion">传递考试类型</param>
        /// <returns>返回题目内容</returns>
        public DataTable LoadIEByFlag(IEQuestionEntity iequestion)
        {
            DataTable ieQuestionDt ;
            string cmdText = "select questionID,questionContent,questionFlag,fraction,paperType,correctAnswer from IEQuestionEntity where paperType=@paperType and questionFlag=@questionFlag";

            SqlParameter[] paras = new SqlParameter[]{new SqlParameter ("@paperType",iequestion.paperType) ,
                new  SqlParameter("@questionFlag",iequestion.questionFlag )
        };
            ieQuestionDt = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.Text);
            return ieQuestionDt;

        }
        #endregion

        #region "根据学号选择题型-韩梦甜-2015-11-20"
        /// <summary>
        /// 根据学号选择题型-韩梦甜-2015-11-20
        /// </summary>
        /// <param name="studentinfo">传递学号</param>
        /// <returns>返回题目内容</returns>
        public DataTable SelectPaperTypeByStudentID(StudentInfoEntity studentinfo)
        {
            DataTable Dt;
            string cmdText = "select paperType from IEQuestionRecordEntity where studentID=@studentinfo";

            SqlParameter[] paras = new SqlParameter[]{new SqlParameter ("@studentinfo",studentinfo.studentID )};
            Dt = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.Text);
            return Dt;

        }
        #endregion
        #region 将答案和分数更新到数据库中  李芬  2015年12月26日10:09:07
        /// <summary>
        /// 将答案和分数更新到数据库中
        /// </summary>
        /// <param name="list">StudentID,QuestionID,ExamAnswer,TimeStamp,fration</param>
        /// <param name="studentrecord">只需要一个studentID，为了查出学院ID</param>
        public void ReturnScore(List<IEQuestionRecordEntity> list, IEQuestionRecordEntity studentrecord)
        {
            String which = WhichIERecored(studentrecord);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" update IEQuestionRecordEntity_" + which + " SET fraction = CASE questionID ");
            string Fation = string.Empty;
            string questionID = string.Empty;
            string studentIDs = string.Empty;
            string tamstamp = string.Empty;

            //根据学号，和时间戳选出这一段的内容，然后再根据QuestionID给每一个flag更新分数。
            #region 更新分数的拼接sql语句
            for (int i = 0; i < list.Count; i++)
            {
                questionID = list[i].questionID.ToString();
                //将分数传给变量
                Fation = list[i].fraction.ToString();
                //判分sql，拼接sql，用case，when语句
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + Fation + "'");
            }
            sbSql.Append(" end,"); //加“，” 
            #endregion

            #region 拼接考生答案更新到数据库中
            //拼接更新考生答案sql
            sbSql.Append("  examAnswer = CASE questionID");
            for (int i = 0; i < list.Count; i++)
            {
                string examAnswer = list[i].examAnswer.ToString();
                questionID = list[i].questionID.ToString();
                //学生答案信息更新，when后边的是QuerstionID,
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + list[i].examAnswer + "'");
            }
            sbSql.Append(" end");   //不加逗号“，” 
            #endregion         
            studentIDs += "'" + list[0].studentID.ToString() + "'";
            tamstamp += "'" + list[0].timeStamp.ToString() + "'";
            
            studentIDs.Remove(studentIDs.Length - 1, 1);
            sbSql.Append(" where studentID in (" + studentIDs + ") and timeStamp in (" + tamstamp + ")");
            DataTable dt = sqlhelper.ExecuteQuery(sbSql.ToString(), CommandType.Text);
        }
        #endregion

       

        #region 查找所有的IE的套卷 IEPaperType() 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的word的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable IEPaperType()
        {
            DataTable dt;
            string sql = "select Distinct paperType from IEQuestionEntity";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 给WordQutionEntity分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的word的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable IEPaperTypeGroupByPaperType()
        {
            DataTable dt;
            string sql = "select * from IEQuestionEntity group by paperType";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion


        #region 根据PaperType查询分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        ///给WordQutionEntity分组
        /// </summary>
        /// <returns></returns>
        public DataTable IEPaperTypeGroupByPaperType(String PaperType)
        {
            DataTable dt;
            string sql = "select * from IEQuestionEntity where paperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@PaperType",PaperType )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion


        #region 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// <summary>
        /// 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertIERecord(IEQuestionRecordEntity studentrecord)
        {
            String which = WhichIERecored(studentrecord);
            string sql = "Insert into IEQuestionRecordEntity_" + which + "(questionID,studentID,paperType,questionContent,correctAnswer) values(@QuestionID,@StudentID,@PaperType,@QuestionContent,@RightAnswer)";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.studentID ),
                new SqlParameter ("@PaperType",studentrecord.paperType ),
                new SqlParameter ("@QuestionContent",studentrecord.questionContent),
                new SqlParameter ("@RightAnswer",studentrecord.correctAnswer),
                new SqlParameter ("@QuestionID",studentrecord.questionID )
            };
            int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            return flag;
        }
        #endregion

        #region 根据学生的ID查询去重--邱慕夏  2015年11月23日14:28:57
        /// <summary>
        /// 根据学生的ID查询去重--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否已经添加</returns>
        public Boolean SelectIERecord(IEQuestionRecordEntity studentrecord)
        {
            String which = WhichIERecored(studentrecord);
            string sql = "Select * from  IEQuestionRecordEntity_" + which + " where studentID=@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.studentID ),
            };
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            if (dt.Rows.Count == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// <summary>
        /// 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertIERecordList(List<IEQuestionRecordEntity> studentrecordlist, StudentInfoEntity student)
        {
          


            int flag = 0;
            if (studentrecordlist.Count != 0)
            {
                String which = student.CollegeID;
                String sql = "Insert into IEQuestionRecordEntity_" + which + "(questionID,studentID,paperType,questionContent,correctAnswer,timeStamp) ";
                for (int i = 0; i < studentrecordlist.Count; i++)
                {
                    if (i != studentrecordlist.Count - 1)
                    {
                        sql = sql + "select '" + studentrecordlist[i].questionID + "','" + studentrecordlist[i].studentID + "','" + studentrecordlist[i].paperType + "','" + studentrecordlist[i].questionContent + "','" + studentrecordlist[i].correctAnswer + "','" + studentrecordlist[i].timeStamp + "' union all ";
                    }
                    else
                    {
                        sql = sql + "select '" + studentrecordlist[i].questionID + "','" + studentrecordlist[i].studentID + "','" + studentrecordlist[i].paperType + "','" + studentrecordlist[i].questionContent + "','" + studentrecordlist[i].correctAnswer + "','" + studentrecordlist[i].timeStamp + "'; ";
                    }
                
                }
                flag = sqlhelper.ExecuteNonQuery(sql, CommandType.Text);
                return flag;
            }
            else
            {
                return 1;
            }

        }
        #endregion

        #region 看看向哪个数据库表中进行填写数据--邱慕夏 -2015年11月23日14:17:36
        /// <summary>
        /// 看看向哪个数据库表中进行填写数据
        /// </summary>
        /// <param name="studentrecord"></param>
        /// <returns></returns>
        public String WhichIERecored(IEQuestionRecordEntity studentrecord)
        {
            DataTable dt;
            string sql = "select collegeId from StudentInfoEntity where studentID =@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.studentID),
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            String which = dt.Rows[0][0].ToString();
            return which;
        }
        #endregion

        #region 查询该考生 是否有资格考试 -赵崇-2015年11月24日 16:39:57
        /// <summary>
        /// 查询该考生 是否有资格考试
        /// </summary>
        /// <param name="pEnStudent">学生</param>
        /// <returns>true表示可以进行考试，false表示 未进行配置 所以不能考试</returns>
        public bool GetIsCanExamByStudent(StudentInfoEntity pEnStudent)
        {

            string tableName = "";
            tableName = "IEQuestionRecordEntity_" + pEnStudent.CollegeID;

            //1,查询所有的选择题
            DataTable dt;
            string sql = "select * from " + tableName + " where StudentID=@StudentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter ("@StudentID",pEnStudent.studentID )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);

            if (dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }
        #endregion

        #region 将Excel表中的IE操作题导入到题库中——陈新月——2018-04-02
        public int InsertNewIEQuestion(List<IEQuestionEntity> lstIEQuestion)
        {
            //查询所有的IE题数目，以此作为题号+1
            DataTable dt = new DataTable();
            string sqlCount = "select * from IEQuestionEntity";
            dt = sqlhelper.ExecuteQuery(sqlCount, CommandType.Text);

            int i=0;
            int j = 0;
            for (i = 0; i < lstIEQuestion.Count; i++)
            {
                string sql = "insert into IEQuestionEntity(questionID,questionTypeID,paperType,fraction,questionFlag,questionContent,correctAnswer,isUse) values(@questionID,@questionTypeID,@paperType,@fraction,@questionFlag,@questionContent,@correctAnswer,@isUse)";

                lstIEQuestion[i].questionID = (double)(dt.Rows.Count + i + 1);
                SqlParameter[] paras = new SqlParameter[] {
                    new SqlParameter("@questionID",lstIEQuestion[i].questionID),
                    new SqlParameter("@questionTypeID","6"),
                    new SqlParameter("@paperType",lstIEQuestion[i].paperType),
                    new SqlParameter("@fraction",lstIEQuestion[i].fraction),
                    new SqlParameter("@questionFlag",lstIEQuestion[i].questionFlag),
                    new SqlParameter("@questionContent",lstIEQuestion[i].questionContent),
                    new SqlParameter("@correctAnswer",lstIEQuestion[i].correctAnswer),
                    new SqlParameter("@isUse","On")
                };
                try
                {
                    //捕获错误数据的标志
                    int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
                    j++;
                }
                catch (Exception)
                {
                    //返回错误数据的标志
                    return j;
                }
                
            }
            return j;
        }
        #endregion

        #region 查询试卷类型——陈新月——2018-04-08
        public DataTable InqueryPaperType()
        {
            DataTable dt = new DataTable();
            string sql = "select top 8 * from IEquestionEntity";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据试卷类型查询试卷分数配置——陈新月——2018-04-16
        public DataTable InqueryIEInfo(IEQuestionEntity ieQuestion)
        {
            DataTable dt;
            string sql = "select fraction as 分值,questionFlag as 题型标志 from IEQuestionEntity where paperType=@paperType";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@paperType",ieQuestion.paperType)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据试卷类型更新试卷分数配置——陈新月——2018-04-17
        public int UpdateIEInfo(List<IEQuestionEntity> listIE)
        {
            int UpdateIE = 0;
            for (int i = 0; i < listIE.Count; i++)
            {
                string sql = "update IEQuestionEntity set fraction=@fraction,questionFlag=@questionFlag where paperType=@paperType and questionFlag=@questionFlag";
                SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@paperType",listIE[i].paperType),
                new SqlParameter("@fraction",listIE[i].fraction),
                new SqlParameter("@questionFlag",listIE[i].questionFlag)
                };
                UpdateIE = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);

            }
            return UpdateIE;
        }
        #endregion
    }
}
